package cn.easier.activity.console.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ReadExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ReadExcelUtil.class);

    /**
     * 读取Excel文件
     *
     * @param excel Excel文件
     */
    public static List<List<String>> readExcel(File excel) {
        //读取后的数据
        List<List<String>> dataLst = new ArrayList<List<String>>(); // 保存每一行数据的list集合
        try {
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在
                String excelName = excel.getName();
                String suffix = excelName.substring(excelName.lastIndexOf(".") + 1);
                Workbook wb;

                //根据文件后缀（xls/xlsx）进行判断
                if ("xls".equals(suffix)) {
                    FileInputStream fis = new FileInputStream(excel);   //文件流对象
                    wb = new HSSFWorkbook(fis);
                    try {
                        fis.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                } else if ("xlsx".equals(suffix)) {
                    wb = new XSSFWorkbook(excel);
                } else {
                    logger.error("excel文件解析失败：不是excel文件格式");
                    return null;
                }


                //开始解析，只解析sheet0。
                Sheet sheet = wb.getSheetAt(0);
                //第一行是列名，不参与数据读取
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum();
                logger.info("总计: {}行有效数据(含空白行)", lastRowIndex);

                //第一行不参加数据录入，用来定位列位置。
                Row firstRow = sheet.getRow(sheet.getFirstRowNum());
                int firstCellIndex = -1;
                int lastCellIndex = -1;

                if (firstRow != null && firstRow.getFirstCellNum() >= 0 && firstRow.getLastCellNum() >= 0) {
                    firstCellIndex = firstRow.getFirstCellNum();
                    lastCellIndex = firstRow.getLastCellNum() - 1;
                }
                if (firstCellIndex < 0 || lastCellIndex < 0 || firstCellIndex > lastCellIndex) {
                    logger.info("读取文件列失败，文件列读取失败");
                    return null;
                }
                logger.info("总计: {}列有效数据(含空白列)", lastCellIndex + 1 - firstCellIndex);

                for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
                    Row row = sheet.getRow(rIndex);
                    List<String> cells = new ArrayList<String>();
                    //判断行是否存在，并且是不是空白行
                    if (row != null && row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) {
                        for (int cIndex = firstCellIndex; cIndex <= lastCellIndex; cIndex++) {   //遍历列
                            Cell cell = row.getCell(cIndex);
                            if (null != cell) {
                                cells.add(getCellValue(cell));
                            } else {
                                cells.add("");
                            }
                        }
                    }
                    //如果有数据则录入，防止sheet存在row对象，但是为空白数据。
                    if (cells.size() > 0) {
                        dataLst.add(cells);
                    }
                }
                try {
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                logger.error("找不到指定的Excel文件");
            }
        } catch (Exception e) {
            logger.error("excel文件解析异常");
            e.printStackTrace();
        }
        return dataLst;
    }

    /**
     * 获取cell值
     *
     * @param cell cell
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

//    public static void main(String[] args) {
//        String excelPath = "D:\\test.xlsx";
//        File excel = new File(excelPath);
//        List<List<String>> dataList = readExcel(excel);
//        if (dataList != null && dataList.size() > 1) {
//            logger.info("row size: {}", dataList.size());
//            List<String> cells = dataList.get(0);
//            if (cells != null && cells.size() > 0) {
//                logger.info("cell size: {}", cells.size());
//            }
//        }
//    }

}
